package com.mzj.saas.mservice.excel.service;

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.mzj.saas.commons.DownloadUtil;
import com.mzj.saas.mservice.excel.util.ExcelToPdf;
import com.mzj.saas.mservice.excel.vo.ExcelTemplateVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;


/**
 * Excel模板生成excel
 * 
 * @author shun
 * @createDate: 2022年6月27日 下午3:39:10
 * @version: 1.0
 *
 */
@Slf4j
@Service
public class ExcelService {

	@Value("${bill.template.path}")
	private String billTemplatePath;
	@Value("${bill.upload.path}")
	private String billUploadPath;
			
	/**
	 * 按模板生成EXCEL文件
	 * 
	 * @param response
	 * @throws IOException
	 * @author shun
	 * @date 2022年6月27日
	 * 按模板生成账单
	 */
	public void exportTemplateExcel(ExcelTemplateVo vo, HttpServletResponse response) throws IOException {
		OutputStream out = null;
		BufferedOutputStream bos = null;
		// excel
		File excelFile = null;
		// pdf
		File pdfFile = null;
		try {
			String templateFileName = billTemplatePath + "lease_template.xlsx";
			excelFile = new File(billUploadPath+ "退租账单.xlsx");
			InputStream in = this.getClass().getClassLoader().getResourceAsStream("static/exceltemplates/lease_template.xlsx");
			//读文件
			XSSFWorkbook workbook = new XSSFWorkbook(in);
			Sheet sheet = workbook.getSheetAt(0);
			Map<String, Object> map = new HashMap<>();
			map.put("communityName", vo.getCommunityName());
			map.put("buildingName", vo.getBuildingName());
			map.put("unitName", vo.getUnitName());
			map.put("roomNumber", vo.getRoomNumber());
			map.put("startTime", vo.getStartTime());
			map.put("afterTime", vo.getAfterTime());
			map.put("billingArea", vo.getBillingArea().toString());
			map.put("rentTotalPrice", vo.getRentTotalPrice().toString());
			map.put("depositTotalPrice", vo.getDepositTotalPrice().toString());
			map.put("otherTotalPrice", vo.getOtherTotalPrice().toString());
			map.put("payable", vo.getPayable());
			map.put("orgName", vo.getOrgName());
			//替换内容
			replaceSheet(map, workbook, sheet, null);
			FileOutputStream outputStream = new FileOutputStream(excelFile);
			workbook.write(outputStream);


			// EXCEL转PDF
			String pdfPath = billUploadPath + "退租账单.pdf";
			ExcelToPdf.excel2pdf(excelFile.getPath(), pdfPath, billUploadPath);

			// 下载
			pdfFile = new File(pdfPath);
			excelDownload(pdfFile, response);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		} finally {
			// 处理完以后删除临时文件
			if (excelFile != null) {
				excelFile.delete();
			}

			if (pdfFile != null) {
				pdfFile.delete();
			}
		}
	}

	/**
	 * 下载, 响应页面文件流
	 *
	 * @param file
	 * @param response
	 * @author shun
	 * @date 2021年5月31日
	 */
	public void excelDownload(File file, HttpServletResponse response) {
		try {
			// 读到流中
			InputStream in = new FileInputStream(file);
			response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");

			// 导出文件下载
			DownloadUtil downloadUtil = new DownloadUtil();
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			byte[] b = new byte[1024];
			int len;
			try {
				while ((len = in.read(b)) > 0) {
					bos.write(b, 0, len);
				}

				in.close();
			} catch (IOException e) {
				log.error("Download the document is failed,message:{}", e.getMessage());
			}

			downloadUtil.download(bos, response, file.getName());
		} catch (IOException e) {
			log.error(e.getMessage(), e);
		} finally {
			// 删除本地临时文件
			if (file != null && file.exists()) {
				file.delete();
			}
		}
	}



	/**
	 * 设置样式 去除默认表头样式及设置内容居中
	 * 
	 * @return
	 * @author shun
	 * @date 2022年6月27日
	 */
	public static HorizontalCellStyleStrategy getStyleStrategy() {
		// 内容样式策略
		WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
		// 垂直居中,水平居中
		contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
		contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
		contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
		contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
		contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
		// 设置 自动换行
		contentWriteCellStyle.setWrapped(true);
		// 字体策略
		WriteFont contentWriteFont = new WriteFont();
		// 字体大小
		contentWriteFont.setFontHeightInPoints((short) 12);
		contentWriteCellStyle.setWriteFont(contentWriteFont);
		// 头策略使用默认
		WriteCellStyle headWriteCellStyle = new WriteCellStyle();
		headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
		return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
	}


	/**
	 * 替换Excel模板文件内容
	 *
	 * @param replaceMap 文档数据
	 */
	public static void replaceSheet(Map<String, Object> replaceMap, Workbook workbook, Sheet sheet, Integer endRowIndex) {
		try {
			Iterator rows = sheet.rowIterator();

			while (rows.hasNext()) {
				Row row = (Row) rows.next();
				if (row == null) {
					continue;
				}
				int num = row.getLastCellNum();
				if (endRowIndex != null && row.getRowNum() > endRowIndex) {
					break;
				}

				for (int i = 0; i < num; i++) {
					Cell cell = row.getCell(i);
					if (cell == null || cell.getStringCellValue() == null) {
						continue;
					}
					String value = cell.getStringCellValue();

					if (StrUtil.isNotEmpty(value) && value.indexOf("{") != -1) {
						cell.setCellValue(replaceValue(value, replaceMap));
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 替换内容 ${}
	 *
	 * @param value
	 * @param replaceMap
	 * @return
	 */
	public static String replaceValue(String value, Map<String, Object> replaceMap) {

		for (String name : replaceMap.keySet()) {
			boolean contains = value.contains("{" + name + "}");
			if (contains){
				value = value.replace("{" + name + "}", (String) replaceMap.get(name));
			}
		}

		return value;
	}



}
